import openpyxl
import requests  # 爬取网页
import json
import pymysql

url = 'https://c.m.163.com/ug/api/wuhan/app/data/list-total?t=329822670771'
headers = {
    'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/97.0.4692.71 Safari/537.36 Edg/97.0.1072.55'}
response = requests.get(url, headers=headers)
#连接数据库
conn=pymysql.connect(
    host="127.0.0.1",
    port=3306,#端口号
    user="root",#数据库用户
    password="123456",#数据库密码
    database="bysj"#要连接的数据库名称
)
cursor=conn.cursor()

sql_insert="""insert into bysj.pq1 (area, addconfirm, confirm, dead, heal,data) values(%s,%s,%s,%s,%s,%s)"""
sql_insert2="""truncate table bysj.pq1"""
cursor.execute(sql_insert2)
conn.commit()

wb = openpyxl.Workbook()
sht = wb.create_sheet("世界疫情表")
sht.cell(row=1, column=1).value = "地区"
sht.cell(row=1, column=2).value = "新增确诊"
sht.cell(row=1, column=3).value = "累计确诊"
sht.cell(row=1, column=4).value = "死亡"
sht.cell(row=1, column=5).value = "治愈"
sht.cell(row=1, column=6).value = "时间"
json_data = response.json()['data']['areaTree']
for i in range(206):
    earth_data = json_data[i]
    name = earth_data['name']
    sht.cell(row=i+2,column=1).value = name
    today_confirm = json.dumps(earth_data['today']['confirm'])
    sht.cell(row=i+2,column=2).value = today_confirm
    total_confirm = json.dumps(earth_data['total']['confirm'])
    sht.cell(row=i+2,column=3).value = total_confirm
    total_dead = json.dumps(earth_data['total']['dead'])
    sht.cell(row=i+2,column=4).value = total_dead
    total_heal = json.dumps(earth_data['total']['heal'])
    sht.cell(row=i+2,column=5).value = total_heal
    date = earth_data['lastUpdateTime']
    sht.cell(row=i+2,column=6).value = date
    content=(name,today_confirm,total_confirm,total_dead,total_heal,date)
    cursor.executemany(sql_insert, [content])
    conn.commit()

wb.save(r'E:\bysj\diango\exc\疫情.xlsx')
wb.close()
